// PetService.java
package org.example;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class PetSelect {

    public List<Pet> listAvailablePets() throws SQLException {
        List<Pet> pets = new ArrayList<>();
        String sql = "SELECT * FROM pets WHERE status = 'available'";

        try (Connection conn = JDBCDemo.getConnection();
             Statement stmt = conn.createStatement();
             ResultSet rs = stmt.executeQuery(sql)) {

            while (rs.next()) {
                int id = rs.getInt("id");
                String name = rs.getString("name");
                String breed = rs.getString("breed");
                int age = rs.getInt("age");
                int price = rs.getInt("price");
                String description = rs.getString("description");
                String speciesStr = rs.getString("species");

                Pet pet;
                if ("狗".equals(speciesStr)) {
                    pet = new Dog(id, name, breed, age, price, true, description);
                } else if ("猫".equals(speciesStr)) {
                    pet = new Cat(id, name, breed, age, price, true, description);
                } else if ("仓鼠".equals(speciesStr)) {
                    pet = new CangShu(id, name, breed, age, price, true, description);
                } else {
                    System.out.println("警告：跳过未知物种 [" + speciesStr + "]，ID=" + id);
                    continue;
                }

                pets.add(pet);
            }
        }
        return pets;
    }

    public List<Pet> filterPetsByKind(String kind) throws SQLException {
        List<Pet> pets = new ArrayList<>();
        String sql = "SELECT * FROM pets WHERE species = ? AND status = 'available'";

        try (Connection conn = JDBCDemo.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setString(1, kind);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    String speciesStr = rs.getString("species");

                    Pet pet;
                    if ("狗".equals(speciesStr)) {
                        pet = new Dog(
                                rs.getInt("id"),
                                rs.getString("name"),
                                rs.getString("breed"),
                                rs.getInt("age"),
                                rs.getInt("price"),
                                true,
                                rs.getString("description")
                        );
                    } else if ("猫".equals(speciesStr)) {
                        pet = new Cat(
                                rs.getInt("id"),
                                rs.getString("name"),
                                rs.getString("breed"),
                                rs.getInt("age"),
                                rs.getInt("price"),
                                true,
                                rs.getString("description")
                        );
                    } else if ("仓鼠".equals(speciesStr)) {
                        pet = new CangShu(
                                rs.getInt("id"),
                                rs.getString("name"),
                                rs.getString("breed"),
                                rs.getInt("age"),
                                rs.getInt("price"),
                                true,
                                rs.getString("description")
                        );
                    } else {
                        continue;
                    }
                    pets.add(pet);
                }
            }
        }
        return pets;
    }

    public List<Pet> filterPetsByPriceRange(int min, int max) throws SQLException {
        List<Pet> pets = new ArrayList<>();
        String sql = "SELECT * FROM pets WHERE price BETWEEN ? AND ? AND status = 'available'";

        try (Connection conn = JDBCDemo.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setInt(1, min);
            ps.setInt(2, max);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    String species = rs.getString("species");
                    Pet pet;
                    if ("狗".equals(species)) {
                        pet = new Dog(rs.getInt("id"),
                                rs.getString("name"),
                                rs.getString("breed"),
                                rs.getInt("age"),
                                rs.getInt("price"),
                                true,
                                rs.getString("description"));
                    } else if ("猫".equals(species)) {
                        pet = new Cat(
                                rs.getInt("id"),
                                rs.getString("name"),
                                rs.getString("breed"),
                                rs.getInt("age"),
                                rs.getInt("price"),
                                true,
                                rs.getString("description"));
                    } else if ("仓鼠".equals(species)) {
                        pet = new CangShu(
                                rs.getInt("id"),
                                rs.getString("name"),
                                rs.getString("breed"),
                                rs.getInt("age"),
                                rs.getInt("price"),
                                true,
                                rs.getString("description"));
                    } else {
                        continue;
                    }
                    pets.add(pet);
                }
            }
        }
        return pets;
    }

    public List<Pet> getPetByName(String name) throws SQLException {
        List<Pet> pets = new ArrayList<>();
        String sql = "SELECT * FROM pets WHERE name = ? AND status = 'available'";

        try (Connection conn = JDBCDemo.getConnection();
             PreparedStatement ps = conn.prepareStatement(sql)) {

            ps.setString(1, name);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    String speciesStr = rs.getString("species");
                    int id = rs.getInt("id");
                    String petName = rs.getString("name");
                    String breed = rs.getString("breed");
                    int age = rs.getInt("age");
                    int price = rs.getInt("price");
                    String description = rs.getString("description");

                    Pet pet;
                    if ("狗".equals(speciesStr)) {
                        pet = new Dog(id, petName, breed, age, price, true, description);
                    } else if ("猫".equals(speciesStr)) {
                        pet = new Cat(id, petName, breed, age, price, true, description);
                    } else if ("仓鼠".equals(speciesStr)) {
                        pet = new CangShu(id, petName, breed, age, price, true, description);
                    } else {
                        System.out.println("警告：跳过未知物种 [" + speciesStr + "]，ID=" + id);
                        continue;
                    }
                    pets.add(pet);
                }
            }
        }
        return pets;
    }
}